package com.sdy.healthcode.biz.mapper;

import com.sdy.healthcode.biz.model.Region;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface RegionMapper {
    /**
     * 获取省市区街道的数据
     */
    @Select("select s3.province_code as provinceCode, s3.province_name as provinceName, " +
            "    s2.city_code as cityCode, s2.city_name as cityName, " +
            "    s1.area_code as areaCode, s1.area_name as areaName, " +
            "    s.street_code as streetCode, s.street_name as streetName from bs_street s " +
            "left join bs_area s1 on s1.area_code = s.area_code " +
            "left join bs_city s2 on s2.city_code = s1.city_code " +
            "left join bs_province s3 on s3.province_code = s2.province_code " +
            "where s3.province_name = #{provinceName} and s2.city_name = #{cityName}")
    List<Region> listRegionByProvinceAndCity(@Param("provinceName") String provinceName, @Param("cityName") String cityName);

    /**
     * 获取省市区数据
     */
    @Select("select s3.province_code as provinceCode, s3.province_name as provinceName, " +
            "    s2.city_code as cityCode, s2.city_name as cityName, " +
            "    s1.area_code as areaCode, s1.area_name as areaName " +
            "from bs_area s1 " +
            "left join bs_city s2 on s2.city_code = s1.city_code " +
            "left join bs_province s3 on s3.province_code = s2.province_code " +
            "where s3.province_name = #{provinceName} and s2.city_name = #{cityName}")
    List<Region> listAreaByProvinceAndCity(String provinceName, String cityName);

    /**
     * 获取区县所有社区信息
     */
    @Select("select * from bs_community s1 " +
            "left join bs_street s2 on s2.street_code = s1.street_code " +
            "left join bs_area s3 on s3.area_code = s2.area_code " +
            "left join bs_city s4 on s4.city_code = s3.city_code  " +
            "left join bs_province s5 on s5.province_code = s4.province_code   " +
            "where s3.area_code = #{areaCode} or s4.city_code = #{areaCode} or s5.province_code = #{areaCode} order by s1.community_code " +
            "limit ${start}, ${count}")
    List<Region> listCommunityByArea(@Param("areaCode") String areaCode, @Param("start") Long start, @Param("count") Long count);
    /**
     * 获取区县所有社区信息(count)
     */
    @Select("select count(*) from bs_community s1 " +
            "left join bs_street s2 on s2.street_code = s1.street_code " +
            "left join bs_area s3 on s3.area_code = s2.area_code " +
            "left join bs_city s4 on s4.city_code = s3.city_code  " +
            "left join bs_province s5 on s5.province_code = s4.province_code   " +
            "where s3.area_code = #{areaCode} or s4.city_code = #{areaCode} or s5.province_code = #{areaCode} order by s1.community_code")
    Integer listCommunityByAreaCount(@Param("areaCode") String areaCode);

    @Select("select province_code as provinceCode, province_name as provinceName from bs_province where province_name = #{provinceName}")
    Region getProvince(@Param("provinceName") String provinceName);

    @Select("select city_code as cityCode, city_name as cityName from bs_city where city_name = #{cityName}")
    Region getCity(@Param("cityName") String cityName);
}
